package com.gph.saviorframework.poi;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * POI的帮助类
 */
public class POIUtils {

    /**
     * 打开excel文件
     * @param excelTemplateFile
     * @return
     */
    public static Workbook openWorkBook(File excelTemplateFile){
        try {
            if(excelTemplateFile.exists()){
                return getWorkBookByFile(excelTemplateFile);
            }
            else{
                return null;
            }
        }
        catch (Exception ex){
            ex.printStackTrace();
            return null;
        }
    }

    public static Workbook openWorkBook(String filePath){
        File excelTemplateFile = new File(filePath);
        try {
            if(excelTemplateFile.exists()){
                return getWorkBookByFile(excelTemplateFile);
            }
            else{
                return null;
            }
        }
        catch (Exception ex){
            ex.printStackTrace();
            return null;
        }
    }

    /**
     * 根据输入流ins获取WorkBook对象
     *
     * @param ins 输入流
     * @return workbook
     * @throws Exception
     */
    private static Workbook getWorkBookByStream(InputStream ins) throws Exception {
        return WorkbookFactory.create(ins);
    }

    /**
     * 根据文件获取workbook对象
     * @param file
     * @return
     * @throws Exception
     */
    private static Workbook getWorkBookByFile(File file) throws Exception{
        return WorkbookFactory.create(file);
    }

    /**
     * 根据Workbook,sheetIndex获取sheet对象
     *
     * @param book WorkBook对象
     * @param number sheetIndex
     * @return sheet
     * @throws Exception
     */
    public static Sheet getSheetByNum(Workbook book, int number) throws Exception {
        return book.getSheetAt(number);
    }

    /**
     * 根据 Workbook对象返回该Workbook对象中所有sheet的Map数组.
     *
     * @param book
     * @return Map<sheetIndex , sheetName>
     * @throws Exception
     */
    public static Map<Integer, String> getSheetNameByBook(Workbook book) throws Exception {
        Map<Integer, String> map = new LinkedHashMap<Integer, String>();
        int sheetNum = book.getNumberOfSheets();
        for (int indexSheet = 1; indexSheet <= sheetNum; indexSheet++) {
            Sheet sheet = getSheetByNum(book, indexSheet);
            map.put(indexSheet, sheet.getSheetName());
        }
        return map;
    }

    /**对row的操作**/

    /**
     * 获取row
     * @param sheet
     * @param index
     * @return
     */
    public static Row getRowByIndex(Sheet sheet,int index){
        return sheet.getRow(index);
    }

    /**
     * 创建一个新行
     * @param sheet
     * @param index
     */
    public static Row createRow(Sheet sheet,int index){
        return sheet.createRow(index);
    }

    /**
     * 复制行
     * @param sheet
     * @param index 原始行的index
     * @param destIndex 目标行的index
     * @return
     */
    public static Row copyRow(Sheet sheet,int index,int destIndex){
        Row row = getRowByIndex(sheet, index);
        if(row!=null){
            //创建一个新的行
            Row newRow = createRow(sheet, destIndex);

            for (Iterator cellIt = row.cellIterator(); cellIt.hasNext();) {
                Cell fromCell = (Cell)cellIt.next();
                Cell distCell = newRow.createCell(fromCell.getColumnIndex());
                copyCell(fromCell,distCell);
            }
            return newRow;
        }
        return null;
    }


    /**
     * 复制单元格
     * @param srcCell
     * @param distCell
     */
    private static void copyCell(Cell srcCell, Cell distCell) {
        distCell.setCellStyle(srcCell.getCellStyle());
        if (srcCell.getCellComment() != null) {
            distCell.setCellComment(srcCell.getCellComment());
        }
        int srcCellType = srcCell.getCellType();
        distCell.setCellType(srcCellType);
        if (srcCellType == Cell.CELL_TYPE_NUMERIC) {
            if (DateUtil.isCellDateFormatted(srcCell)) {
                distCell.setCellValue(srcCell.getDateCellValue());
            }
            else {
                distCell.setCellValue(srcCell.getNumericCellValue());
            }
        }
        else if (srcCellType == Cell.CELL_TYPE_STRING) {
            distCell.setCellValue(srcCell.getRichStringCellValue());
        }
        else if (srcCellType == Cell.CELL_TYPE_BLANK) {
            // nothing21
        }
        else if (srcCellType == Cell.CELL_TYPE_BOOLEAN) {
            distCell.setCellValue(srcCell.getBooleanCellValue());
        }
        else if (srcCellType == Cell.CELL_TYPE_ERROR) {
            distCell.setCellErrorValue(srcCell.getErrorCellValue());
        }
        else if (srcCellType == Cell.CELL_TYPE_FORMULA) {
            distCell.setCellFormula(srcCell.getCellFormula());
        }
        else {
            // nothing29
        }
    }

    /**
     * 用于将Excel表格中列号字母转成列索引，从1对应A开始
     *
     * @param column
     *            列号
     * @return 列索引
     */
    public static int columnToIndex(String column) {
        if (!column.matches("[A-Z]+")) {
            try {
                throw new Exception("Invalid parameter");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        int index = 0;
        char[] chars = column.toUpperCase().toCharArray();
        for (int i = 0; i < chars.length; i++) {
            index += ((int) chars[i] - (int) 'A' + 1)
                    * (int) Math.pow(26, chars.length - i - 1);
        }
        return index;
    }

    /**
     * 用于将excel表格中列索引转成列号字母，从A对应1开始
     *
     * @param index
     *            列索引
     * @return 列号
     */
    public static String indexToColumn(int index) {
        if (index <= 0) {
            try {
                throw new Exception("Invalid parameter");
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }
        index--;
        String column = "";
        do {
            if (column.length() > 0) {
            index--;
        }
            column = ((char) (index % 26 + (int) 'A')) + column;
            index = (int) ((index - index % 26) / 26);
        }
        while (index > 0);
        return column;
    }

    public static String getCellValue(Row row,String column){
        int cellIndex = columnToIndex(column)-1;

        return parseExcel(row.getCell(cellIndex));
    }

    private static String parseExcel(Cell cell) {
        String result = new String();
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                    SimpleDateFormat sdf = null;
                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
                            .getBuiltinFormat("h:mm")) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    Date date = cell.getDateCellValue();
                    result = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 58) {
                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    double value = cell.getNumericCellValue();
                    Date date = org.apache.poi.ss.usermodel.DateUtil
                            .getJavaDate(value);
                    result = sdf.format(date);
                } else {
                    double value = cell.getNumericCellValue();
                    CellStyle style = cell.getCellStyle();
                    DecimalFormat format = new DecimalFormat();
                    String temp = style.getDataFormatString();
                    // 单元格设置成常规
                    if (temp.equals("General")) {
                        format.applyPattern("#");
                    }
                    result = format.format(value);
                }
                break;
            case HSSFCell.CELL_TYPE_STRING:// String类型
                result = cell.getRichStringCellValue().toString();
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                result = "";
            default:
                result = "";
                break;
        }
        return result;
    }

}
